/*
 * Copyright (c) 2013. Lorem ipsum dolor sit amet, consectetur adipiscing elit.
 * Morbi non lorem porttitor neque feugiat blandit. Ut vitae ipsum eget quam lacinia accumsan.
 * Etiam sed turpis ac ipsum condimentum fringilla. Maecenas magna.
 * Proin dapibus sapien vel ante. Aliquam erat volutpat. Pellentesque sagittis ligula eget metus.
 * Vestibulum commodo. Ut rhoncus gravida arcu.
 */

package com.jspx.sober.dialect;

import com.jspx.utils.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.jspx.sober.TableModels;
import com.jspx.utils.DateUtil;
import com.jspx.utils.ObjectUtil;
import java.io.InputStream;
import java.sql.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: chenyuan
 * Date: 13-2-19
 * Time: 上午10:43
 *  NULL: NULL value
 * Integer: 值是signed integer 类型，大小可以是1,2,3,4,6,8bytes
 * REAL:  浮点类型
 * TEXT: 以UTF-8, UTF-16BE or UTF-16LE编码存储的字符类型
 * BLOB: 二进制数据
 * [id] integer PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 0
 ,[idx] bigint NOT NULL DEFAULT 0
 ,[context] TEXT
 ,[dataType] TEXT
 ,[namespace] varchar(20)
 , UNIQUE([id])

 */
public class SqliteDialect extends Dialect
{
    private Logger log = LoggerFactory.getLogger(SqliteDialect.class.getName());
    public SqliteDialect()
    {
        standard_SQL.put(SQL_CREATE_TABLE, "<#assign primary_length=" + KEY_PRIMARY_KEY + ".length />" +
                "CREATE TABLE ${" + KEY_TABLE_NAME + "} \n(\n" +
                " <#list column=" + KEY_COLUMN_LIST + ">${column} <#if where=column_has_next>,</#if>\n</#list>" +
                " \n)");
        
        standard_SQL.put(SQL_INSERT, "INSERT INTO ${" + KEY_TABLE_NAME + "} (<#list field=" + KEY_FIELD_LIST + ">${field}<#if where=field_has_next>,</#if></#list>) VALUES (<#list x=1.." + KEY_FIELD_COUNT + ">?<#if x_has_next>,</#if></#list>)");
        standard_SQL.put(SQL_DELETE, "DELETE FROM ${" + KEY_TABLE_NAME + "} WHERE ${" + KEY_FIELD_NAME + "}=<#if where=" + KEY_FIELD_NAME + FIELD_QUOTE + ">'</#if>${" + KEY_FIELD_VALUE + "}<#ifwhere= " + KEY_FIELD_NAME + FIELD_QUOTE + ">'</#if>");
        standard_SQL.put(SQL_DELETE_IN, "DELETE FROM ${" + KEY_TABLE_NAME + "} WHERE ${" + KEY_FIELD_NAME + "} IN (<#list fvalue=" + KEY_FIELD_VALUE + ">'${fvalue}'<#if where=fvalue_has_next>,</#if></#list>)");
        standard_SQL.put(SQL_UPDATE, "UPDATE ${" + KEY_TABLE_NAME + "} SET <#list field=" + KEY_FIELD_LIST + ">${field}=?<#if where=field_has_next>,</#if></#list> WHERE ${" + KEY_FIELD_NAME + "}=<#if where=" + KEY_FIELD_NAME + FIELD_QUOTE + ">'</#if>${" + KEY_FIELD_VALUE + "}<#if where=" + KEY_FIELD_NAME + FIELD_QUOTE + ">'</#if>");

        standard_SQL.put(String.class.getName(), "${" + COLUMN_NAME + "} <#if where=" + COLUMN_LENGTH + "&gt;255>text<#else>varchar(${" + COLUMN_LENGTH + "})</#else></#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> <#if where=" + COLUMN_DEFAULT + ">default '${" + COLUMN_DEFAULT + "}'</#if>");

        standard_SQL.put(Integer.class.getName(), "${" + COLUMN_NAME + "} integer <#if where=" + KEY_FIELD_SERIAL + ">PRIMARY KEY ASC AUTOINCREMENT</#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL<#else> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if></#else></#if>");
        standard_SQL.put("int", "${" + COLUMN_NAME + "} integer <#if where=" + KEY_FIELD_SERIAL + ">PRIMARY KEY ASC AUTOINCREMENT</#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL<#else> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if></#else></#if>");
        standard_SQL.put(Long.class.getName(), "${" + COLUMN_NAME + "} integer <#if where=" + KEY_FIELD_SERIAL + ">PRIMARY KEY ASC AUTOINCREMENT</#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL<#else> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if></#else></#if>");
        standard_SQL.put("long", "${" + COLUMN_NAME + "} integer <#if where=" + KEY_FIELD_SERIAL + ">PRIMARY KEY ASC AUTOINCREMENT</#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL<#else>  default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if></#else></#if>");

        standard_SQL.put(Double.class.getName(), "${" + COLUMN_NAME + "} REAL default <#if where=!" + COLUMN_DEFAULT + ">0<#else>${" + COLUMN_DEFAULT + "}</#else></#if>");
        standard_SQL.put("double", "${" + COLUMN_NAME + "} REAL default <#if where=!" + COLUMN_DEFAULT + ">0<#else>${" + COLUMN_DEFAULT + "}</#else></#if>");
        standard_SQL.put(Float.class.getName(), "${" + COLUMN_NAME + "} REAL <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if>");
        standard_SQL.put("float", "${" + COLUMN_NAME + "} REAL <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if>");

        standard_SQL.put(Boolean.class.getName(), "${" + COLUMN_NAME + "} integer <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> <#if where=" + COLUMN_DEFAULT + ">default ${" + COLUMN_DEFAULT + ".toInt()}</#if>");
        standard_SQL.put(boolean.class.getName(), "${" + COLUMN_NAME + "} integer <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> <#if where=" + COLUMN_DEFAULT + ">default ${" + COLUMN_DEFAULT + ".toInt()}</#if>");

        standard_SQL.put(Date.class.getName(), "${" + COLUMN_NAME + "} datetime DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime'))");
        standard_SQL.put(byte[].class.getName(), "${" + COLUMN_NAME + "} integer");
        standard_SQL.put(InputStream.class.getName(), "${" + COLUMN_NAME + "} blob");
        standard_SQL.put(char.class.getName(), "${" + COLUMN_NAME + "} char <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> <#if where=" + COLUMN_DEFAULT + ">default '${" + COLUMN_DEFAULT + "}'</#if>");

        standard_SQL.put(SQL_DROP_TABLE, "DROP TABLE ${" + KEY_TABLE_NAME + "}");
        standard_SQL.put(FUN_TABLE_EXISTS, "SELECT COUNT(*) AS NUM FROM sqlite_master WHERE type='table' AND name='${" + KEY_TABLE_NAME + "}'");
    }

    public String getLimitString(String sql, int begin, int end, TableModels soberTable)
    {
        int length = end - begin;
        if (length<0) length = 0;
        return sql + " limit " + begin + "," + length;
    }

    public void setPreparedStatementValue(PreparedStatement pstmt, int parameterIndex, Object obj) throws Exception {
        //支持boolean 类型
        if (obj instanceof Boolean) {
            pstmt.setInt(parameterIndex, ObjectUtil.toInt(ObjectUtil.toBoolean(obj)));
            return;
        }
        super.setPreparedStatementValue(pstmt, parameterIndex, obj);
    }

    public boolean supportsSequenceName()
    {
        return false;
    }

    public boolean supportsLimit()
    {
        return true;
    }

    public boolean supportsSavePoints()
    {
        return false;
    }

    public boolean supportsGetGeneratedKeys()
    {
        return true;
    }

    public boolean supportsConcurReadOnly()
    {
        return false;
    }

    public boolean commentPatch()
    {
        return false;
    }


    /**
     * @param rs    数据
     * @param index 索引
     * @return 返回查询结果
     * @throws java.sql.SQLException 异常
     */
    public Object getResultSetValue(ResultSet rs, int index) throws SQLException {
        String typeName = null;
        int colSize = 0;
        try {
            typeName = rs.getMetaData().getColumnTypeName(index).toLowerCase();
            colSize = rs.getMetaData().getColumnDisplaySize(index);

            if (typeName.equalsIgnoreCase("tinyint"))
            {
                return rs.getBoolean(index);
            }
            //短断整型
            if ((typeName.equals("int") && colSize < 4) || typeName.equals("short") || typeName.equals("smallint") || typeName.equals("int2") || (typeName.equalsIgnoreCase("fixed") && colSize < 4)) {
                return rs.getShort(index);
            }

            ///////长整型
            if (typeName.equals("bigserial") || typeName.equals("long") || typeName.equals("bigint") || typeName.equals("int8") || (typeName.equals("fixed") && colSize > 18)) {
                return rs.getInt(index);
            }

            //////////整型
            if (typeName.equals("integer") || typeName.equals("serial") || typeName.contains("int") || (typeName.equals("number") && colSize < 8) || (typeName.equals("fixed") && colSize < 19)) {
                return rs.getInt(index);
            }


            ///////单精度
            if (typeName.equals("money") || typeName.equals("float") || typeName.equals("real") || typeName.equals("binary_float")) {
                return rs.getFloat(index);
            }
            ///////大数值
            if (typeName.contains("decimal")) {
                return rs.getBigDecimal(index);
            }
            ///////双精度
            if (typeName.equals("double") || typeName.equals("double precision") || typeName.equals("real") || typeName.equals("binary_double")) {
                return rs.getDouble(index);
            }

            ///////日期
            if (typeName.equals("date")) {
                java.sql.Date t = rs.getDate(index);
                if (t == null) return DateUtil.empty;
                return new java.util.Date(t.getTime());
            }

            ///////日期时间java.sql.Timestamp
            if (typeName.contains("timestamp") || typeName.equals("datetime")) {
                Object obj = rs.getObject(index);

                if (obj instanceof Long)
                {
                    return new java.util.Date((Long)obj);
                } else
                if (obj instanceof String)
                {
                    try {
                        return StringUtil.toDate((String)obj);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                } else if (obj instanceof Timestamp)
                {
                    Timestamp t = rs.getTimestamp(index);
                    return new java.util.Date(t.getTime());
                } else if (obj instanceof Date)
                {
                    return rs.getDate(index);
                }
            }

            ////////////时间
            if (typeName.equalsIgnoreCase("time")) {
                return rs.getTime(index);
            }

            ///////短字符串
            if (typeName.equals("char") || typeName.equals("text")|| typeName.equals("nvarchar") || typeName.equals("varchar") || typeName.equals("varchar2")) {
                return rs.getString(index);
            }


            ///////二进制类型 文件类型
            if (typeName.equals("bytea") || typeName.equalsIgnoreCase("image") || typeName.contains("blob")  || typeName.equalsIgnoreCase("image") || typeName.equalsIgnoreCase("long byte")
                    || typeName.equalsIgnoreCase("varbinary") || typeName.equalsIgnoreCase("binary") || typeName.equalsIgnoreCase("long byte")) {

                Blob blob = rs.getBlob(index);
                return blob.getBinaryStream();
            }
            return rs.getObject(index);
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("typeName=" + typeName + " size=" + colSize + " columnName=" + rs.getMetaData().getColumnName(index), e);
        }
        return null;
    }


}